package com.wsoft.form.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.StrUtil;
import com.wsoft.constant.FieldKindConstant;
import com.wsoft.core.exception.Asserts;
import com.wsoft.entity.BaseEntity;
import com.wsoft.form.constant.MysqlReservedWordConstant;
import com.wsoft.form.constant.ObjectDefaultColumnConstant;
import com.wsoft.form.dto.WfFormColumnDTO;
import com.wsoft.form.dto.WfFormTableDto;
import com.wsoft.form.entity.WfFormColumnEntity;
import com.wsoft.form.entity.WfFormEntity;
import com.wsoft.form.entity.WfFormTableEntity;
import com.wsoft.form.entity.WfFormTableRelationEntity;
import com.wsoft.form.enums.FormEnum;
import com.wsoft.form.enums.FormStatusEnum;
import com.wsoft.form.manager.*;
import com.wsoft.form.mapper.WfFormOperationMapper;
import com.wsoft.form.mapper.WfFormTableMapper;
import com.wsoft.form.query.table.TableInsertQuery;
import com.wsoft.form.query.table.TableListQuery;
import com.wsoft.form.query.table.TableUpdateQuery;
import com.wsoft.form.service.IWfFormColumnService;
import com.wsoft.form.service.IWfFormTableService;
import com.wsoft.form.vo.TableVO;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 * 自定义表单-数据表 服务实现类
 * </p>
 *
 * @author Z001
 * @since 2024年08月29日 09:11:06
 */
@Service
public class WfFormTableServiceImpl implements IWfFormTableService {

    @Resource
    private WfFormTableManager tableManager;
    @Resource
    private WfFormTableRelationManager relationManager;
    @Resource
    private WfFormTableMapper wfFormTableMapper;
    @Resource
    private WfFormColumnManager columnManager;
    @Resource
    private WfFormTableMapper tableMapper;
    @Resource
    private WfFormColumnLinkManager linkManager;
    @Resource
    private WfFormManager formManager;
    @Resource
    private WfFormOperationMapper operationMapper;
    @Resource
    private IWfFormColumnService columnService;


    /**
     * 空格
     */
    private static final String SPACE = " ";


    /**
     * 创建表
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Long insertTable(TableInsertQuery query) {
        //校验tableName
        checkTableName(query);

        //保存表名
        WfFormTableEntity tableEntity = Convert.convert(WfFormTableEntity.class, query);
        tableManager.save(tableEntity);
        Long tableId = tableEntity.getId();

        //跟新form状态
        WfFormEntity formEntity = formManager.getById(query.getFormId());
        formEntity.setStatus(FormStatusEnum.STRUCTURE.getValue());
        formManager.updateById(formEntity);


        //创建表,并赋值默认表字段
        List<WfFormColumnDTO> defaultColumnSql = defaultColumnSql(query.getIsBmp(),query.getRelationColumn());
        List<WfFormColumnEntity> columnEntityList = defaultColumnSql.stream().map(item -> {
            WfFormColumnEntity convert = Convert.convert(WfFormColumnEntity.class, item);
            convert.setFormId(query.getFormId());
            convert.setTableId(tableId);
            return convert;
        }).collect(Collectors.toList());
        //创建默认字段
        columnManager.saveBatch(columnEntityList);
        Long slaveColumnId = null;
        if(!FormEnum.ZERO.getValue().equals(query.getRelationType())){
            if(StrUtil.isBlank(query.getRelationColumn())){
                //关联字段不能为空
                Asserts.fail("[wform.relation.column.not.empty]");
            }
            slaveColumnId  = columnEntityList.stream().filter(item -> query.getRelationColumn().equals(item.getColumnName())).map(BaseEntity::getId).findAny().get();
        }
        //创建关联关系
        this.createTableRelation(query.getFormId(), tableId,query.getRelationType(),slaveColumnId);

        if(FormEnum.ONE.getValue().equals(query.getIsCreateTable())){
            //创建表
            wfFormTableMapper.createTable(createTableSql(defaultColumnSql), tableEntity.getTableName(), "'" + tableEntity.getTableDesc() + "'");
        }
        return tableEntity.getId();
    }

    /**
     * 修改表
     * @param req
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean updateTable(TableUpdateQuery req) {
        //表名不能修改
        WfFormTableEntity tableEntity = Convert.convert(WfFormTableEntity.class, req);
        tableManager.updateById(tableEntity);

        if(req.getIsBmp() == 1){
            //是否存在工作流字段
            String columnName = tableMapper.getColumnName(tableEntity.getTableName(), ObjectDefaultColumnConstant.BMP_KEY);
            if(StrUtil.isEmpty(columnName)){
                //保存table
                String sql1 = ObjectDefaultColumnConstant.BMP_KEY + " " + "VARCHAR(50)";
                String sql2 = ObjectDefaultColumnConstant.BMP_STATUS + " " + "VARCHAR(50)";
                tableMapper.addColumn(tableEntity.getTableName(),sql1);
                tableMapper.addColumn(tableEntity.getTableName(),sql2);

                //保存column表
                List<WfFormColumnDTO> defaultColumnSql = defaultColumnSql(1, null);
                List<WfFormColumnEntity> columnEntityList = defaultColumnSql.stream()
                        .filter(item-> item.getColumnName().equals( ObjectDefaultColumnConstant.BMP_KEY) ||
                                item.getColumnName().equals( ObjectDefaultColumnConstant.BMP_STATUS))
                        .map(item -> {
                    WfFormColumnEntity convert = Convert.convert(WfFormColumnEntity.class, item);
                    convert.setFormId(tableEntity.getFormId());
                    convert.setTableId(tableEntity.getId());
                    return convert;
                }).collect(Collectors.toList());
                //创建默认字段
                columnManager.saveBatch(columnEntityList);
            }
        }
        return true;
    }

    /**
     * 获取表信息
     * @param tableId
     * @return
     */
    @Override
    public WfFormTableDto getTableInfo(Long tableId) {
        return tableManager.getTableInfo(tableId);
    }


    /**
     * 返回表 所有字段
     *
     * @param tableId
     * @return
     */
    @Override
    public List<WfFormColumnEntity> getColumnList(Long tableId) {
        return columnManager.getColumnList(tableId);
    }



    /**
     * 删除表
     *
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean deleteTable(Long tableId) {
        WfFormTableEntity tableEntity = tableManager.getById(tableId);
        int operationCount = operationMapper.getOperationCount(tableEntity.getTableName());
        if(operationCount > 0 ){
            //存在数据 无法删除
            Asserts.fail("[wfrom.del.table.error]");
        }

        Long formId = tableEntity.getFormId();
        WfFormTableRelationEntity table = relationManager.getMasterTable(formId, tableId, null);
        if(FormEnum.ONE.getValue().equals(table.getIsMaster())){
            //主表
            List<WfFormTableRelationEntity> relation = relationManager.getMasterRelation(formId, table.getTableId());
            if(CollUtil.isNotEmpty(relation)){
                //主表不能被删除
                Asserts.fail("[wform.master.table.del]");
            }
            //删除表关联关系
            relationManager.delByTableId(formId,tableId);
        }else{
            //删除表关联关系
            relationManager.delBySalveTableId(formId,tableId);
        }
        //获取表下所有字段id
        List<Long> columnIdList = columnManager.getColumnList(tableId).stream().map(BaseEntity::getId).collect(Collectors.toList());

        //删除表
        tableManager.removeById(tableId);
        //删除表字段
        columnManager.removeByTableId(tableId);
        //删除表字段 link
        linkManager.remove(columnIdList);
        if(FormEnum.ONE.getValue().equals(tableEntity.getIsCreateTable())){
            //创建了实体表的情况下 才删除实体表
            tableMapper.deleteTable(tableEntity.getTableName());
        }

        return true;
    }

    /**
     * 查询formId下 所有的表
     * @param query
     * @return
     */
    @Override
    public List<TableVO> listTable(TableListQuery query) {
        List<WfFormTableRelationEntity> relationEntityList = relationManager.getByFormId(query.getFormId());
        if(CollUtil.isEmpty(relationEntityList)){
            return new ArrayList<>();
        }
        Set<Long> tableIdList = new HashSet<>();
        Set<Long> columnIdList = new HashSet<>();
        for (WfFormTableRelationEntity relationEntity : relationEntityList) {
            tableIdList.add(relationEntity.getTableId());
            if(null != relationEntity.getSlaveColumnId()){
                columnIdList.add(relationEntity.getSlaveColumnId());
            }
            if(null != relationEntity.getMasterColumnId()){
                columnIdList.add(relationEntity.getMasterColumnId());
            }
        }
        //获取所有相关table
        List<WfFormTableEntity> tableList = tableManager.listByIds(tableIdList);
        //获取所有相关column
        List<WfFormColumnEntity> columnList = columnManager.listByIds(columnIdList);
        Map<Long, String> tableMap = tableList.stream().collect(Collectors.toMap(BaseEntity::getId, WfFormTableEntity::getTableName));
        Map<Long, String> descMap = tableList.stream().collect(Collectors.toMap(BaseEntity::getId, WfFormTableEntity::getTableDesc));
        Map<Long, String> columnMap = columnList.stream().collect(Collectors.toMap(BaseEntity::getId, WfFormColumnEntity::getColumnName));

        List<TableVO> collect = relationEntityList.stream().map(item -> Convert.convert(TableVO.class, item)).collect(Collectors.toList());
        for (TableVO tableVO : collect) {
            tableVO.setTableName(tableMap.get(tableVO.getTableId()));
            tableVO.setMasterTableName(tableMap.get(tableVO.getMasterTableId()));
            tableVO.setMasterColumnName(columnMap.get(tableVO.getMasterColumnId()));
            tableVO.setSlaveTableName(tableMap.get(tableVO.getSlaveTableId()));
            tableVO.setSlaveColumnName(columnMap.get(tableVO.getSlaveColumnId()));
            tableVO.setMasterTableDesc(descMap.get(tableVO.getTableId()));
        }
        return collect;
    }


    //############################### 私有函数 ###############################

    /**
     * 新增表的时候 校验表名
     * @param tableName  表名
     * @param isCreateTable 是否创建实体表
     */
    private void checkTableName(TableInsertQuery query) {
        String tableName = query.getTableName();
        if(FormEnum.ZERO.getValue().equals(query.getIsCreateTable())){
            List<String> mapList = wfFormTableMapper.showTables();
            if(CollUtil.isEmpty(mapList) || !mapList.contains(tableName)){
                //实体表不存在
                Asserts.fail("[wform.table.not.exist]");
            }
        }else{
            List<WfFormTableEntity> tableList = tableManager.getByTableName(tableName);
            if (CollUtil.isNotEmpty(tableList)) {
                //表名已存在
                Asserts.fail("[wform.table.exist]");
            }
        }
        if(! isValidTableName(tableName)){
            //判断表名是否符合mysql规范
            Asserts.fail("[wform.table.name.not.standard]");
        }

        List<WfFormTableRelationEntity> tableList = relationManager.getMasterTable(query.getFormId(), FormEnum.ONE.getValue());
        if(FormEnum.ZERO.getValue().equals(query.getRelationType())){
            //判断是否已经存在主表
            if(CollUtil.isNotEmpty(tableList)){
                //主表只能存在一张
                Asserts.fail("[wform.table.master.one]");
            }
        }else{
            if(CollUtil.isEmpty(tableList)){
                //请先添加主表
                Asserts.fail("[wform.table.master.insert]");
            }
        }
    }



    /**
     * 校验table 是否符合mysql规范
     * @param tableName
     * @return
     */
    private  boolean isValidTableName(String tableName) {
        // 表名不能为空
        if (tableName == null || tableName.isEmpty()) {
            return false;
        }

        // 表名长度不能超过64个字符
        if (tableName.length() > MysqlReservedWordConstant.COLUMN_SIZE) {
            return false;
        }

        // 表名首字符必须是字母或下划线
        char firstChar = tableName.charAt(0);
        if (!Character.isLetter(firstChar) && firstChar != StrUtil.C_UNDERLINE) {
            return false;
        }
        // 表名不能包含空格
        if (tableName.contains(SPACE)) {
            return false;
        }

        // 表名只能包含字母、数字、下划线和美元符号
        for (char c : tableName.toCharArray()) {
            if (!Character.isLetterOrDigit(c) && c != '_' && c != '$') {
                return false;
            }
        }

        // 表名不能是MySQL的保留字
        if (MysqlReservedWordConstant.MYSQL_RESERVED_WORD_PREFIX.contains(tableName.toUpperCase())
        || MysqlReservedWordConstant.DATA_PERM_LIST.contains(tableName.toUpperCase())) {
            return false;
        }

        return true;
    }

    /**
     * 创建表时 新增关联关系
     * @param formId  formID
     * @param tableId 创建表id
     * @param relationType 关联关系
     */
    private void createTableRelation(Long formId,Long tableId,Integer relationType,Long slaveColumnId) {
        if(FormEnum.ZERO.getValue().equals(relationType)){
            //主表
            WfFormTableRelationEntity entity = new WfFormTableRelationEntity();
            entity.setFormId(formId);
            entity.setTableId(tableId);
            entity.setRelationType(relationType);
            entity.setIsMaster(FormEnum.ONE.getValue());
            relationManager.save(entity);
        }else{
            List<WfFormTableRelationEntity> tableList = relationManager.getMasterTable(formId, FormEnum.ONE.getValue());
            if(CollUtil.isEmpty(tableList)){
                return;
            }
            WfFormTableRelationEntity masterTable = tableList.get(0);
            Long materTableId = masterTable.getTableId();
            WfFormColumnEntity masterColumn = columnManager.getByTableIdAndColumnName(masterTable.getTableId(), "id");
            if(null == masterColumn){
                return;
            }
            Long masterColumnId = masterColumn.getId();

            //新增关联表
            WfFormTableRelationEntity entity = new WfFormTableRelationEntity();
            entity.setFormId(formId);
            entity.setTableId(tableId);
            entity.setIsMaster(FormEnum.ZERO.getValue());
            entity.setMasterTableId(materTableId);
            entity.setMasterColumnId(masterColumnId);
            entity.setSlaveTableId(tableId);
            entity.setSlaveColumnId(slaveColumnId);
            entity.setRelationType(relationType);
            relationManager.save(entity);
        }

    }

    /**
     * 组装默认字段
     *
     * @return
     */
    private List<WfFormColumnDTO> defaultColumnSql(Integer isBmp,String otherColumn) {
        List<WfFormColumnDTO> columnList = new ArrayList<>();
        List<String> defaultColumnList = ObjectDefaultColumnConstant.DEFAULT_COLUMN_LIST;
        List<String> defaultColumnNameList = ObjectDefaultColumnConstant.DEFAULT_COLUMN_NAME_LIST;
        List<String> columnTypeList = ObjectDefaultColumnConstant.COLUMN_TYPE_LIST;
        List<String> fullColumnTypeList = ObjectDefaultColumnConstant.FULL_COLUMN_TYPE_LIST;
        List<Integer> fullFieldKind = ObjectDefaultColumnConstant.FILED_KIND;
        //添加工作流 默认字段
        if (FormEnum.ONE.getValue().equals(isBmp)) {
            defaultColumnList = ObjectDefaultColumnConstant.DEFAULT_COLUMN_BMP_LIST;
            defaultColumnNameList = ObjectDefaultColumnConstant.DEFAULT_COLUMN_NAME_BMP_LIST;
            columnTypeList = ObjectDefaultColumnConstant.COLUMN_TYPE_BMP_LIST;
            fullColumnTypeList = ObjectDefaultColumnConstant.FULL_COLUMN_TYPE_BMP_LIST;
            fullFieldKind  = ObjectDefaultColumnConstant.FULL_FILED_KIND;
        }
        //组装默认字段
        for (int i = 0; i < defaultColumnList.size(); i++) {
            WfFormColumnDTO dto = new WfFormColumnDTO();
            dto.setColumnName(defaultColumnList.get(i));
            dto.setColumnComment(defaultColumnNameList.get(i));
            dto.setName(defaultColumnNameList.get(i));
            dto.setColumnType(columnTypeList.get(i));
            dto.setFullColumnType(fullColumnTypeList.get(i));
            dto.setFieldKind(fullFieldKind.get(i));
            //赋值主键id
            if (i == 0) {
                dto.setColumnSort(i);
                dto.setValidIsNull(FormEnum.ZERO.getValue());
                dto.setPrimaryKey(FormEnum.ONE.getValue());
            } else {
                dto.setColumnSort(i + 100);
                dto.setPrimaryKey(FormEnum.ZERO.getValue());
            }
            dto.setIsDefault(FormEnum.ONE.getValue());
            columnList.add(dto);
        }
        //赋值外键
        if(StrUtil.isNotBlank(otherColumn)){
            WfFormColumnDTO dto = new WfFormColumnDTO();
            dto.setColumnName(otherColumn);
            dto.setName("外键");
            dto.setColumnComment("外键");
            dto.setColumnType("bigint");
            dto.setFullColumnType("bigint(32)");
            dto.setFieldKind(FieldKindConstant.INTEGER);
            //外键不能为空
            dto.setValidIsNull(FormEnum.ZERO.getValue());
            dto.setPrimaryKey(FormEnum.ZERO.getValue());
            //外键这边赋值默认字段
            dto.setIsDefault(FormEnum.ONE.getValue());
            dto.setColumnSort(99);
            columnList.add(dto);
        }
        return columnList;

    }

    /**
     * 组装生成表sql
     *
     * @param columnList 字段列表
     * @return list
     */
    private List<String> createTableSql(List<WfFormColumnDTO> columnList) {
        List<String> sqlList = new ArrayList<>();
        columnList = columnList.stream().sorted(Comparator.comparing(WfFormColumnDTO::getColumnSort)).collect(Collectors.toList());
        for (WfFormColumnDTO column : columnList) {
            sqlList.add(createColumnSql(column));
        }
        return sqlList;
    }

    /**
     * 组装字段sql
     *
     * @param column
     * @return
     */
    private String createColumnSql(WfFormColumnDTO column) {
        StringBuilder sb = new StringBuilder();
        sb.append("`").append(column.getColumnName()).append("`").append(SPACE)
                .append(column.getFullColumnType()).append(SPACE);
        if (FormEnum.ZERO.getValue().equals(column.getValidIsNull())) {
            sb.append("not null").append(SPACE);
        } else {
            sb.append("null").append(SPACE);
        }
        sb.append("comment").append(SPACE).
                append("'").append(column.getColumnComment()).append("'");
        if (FormEnum.ONE.getValue().equals(column.getPrimaryKey())) {
            sb.append(SPACE).append("auto_increment primary key");
        }
        return sb.toString();
    }


    public static void main(String[] args) {
        List<String> defaultColumnList = Arrays.asList("id", "org_id", "create_by", "create_time", "update_by", "update_time", "deleted");
        List<String> defaultColumnNameList = Arrays.asList("主键", "组织id", "创建人", "创建时间", "修改人", "修改时间", "逻辑删除");
        List<String> columnTypeList = Arrays.asList("bigint", "bigint", "varchar", "datetime", "varchar", "datetime", "int");
        List<String> fullColumnTypeList = Arrays.asList("bigint(32)", "bigint(32)", "varchar(50)", "datetime", "varchar(50)", "datetime", "int(1)");
        defaultColumnList.set(1, "bmp_key");
        defaultColumnList.set(2, "bmp_status");
        defaultColumnNameList.set(1, "工作流标识");
        defaultColumnNameList.set(2, "工作流状态");
        columnTypeList.set(1, "varchar");
        columnTypeList.set(2, "varchar");
        fullColumnTypeList.set(1, "varchar(50)");
        fullColumnTypeList.set(2, "varchar(50)");

        System.out.println(defaultColumnList);
        System.out.println(defaultColumnNameList);
        System.out.println(columnTypeList);
        System.out.println(fullColumnTypeList);
    }
}
